package tables;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.Properties;

import beans.Utilisateur;

/* 
 * La table alibi contient :
 * int id_annonce (unique) -- int
 * String pseudo -- varchar(20)
 * Date date_a -- date
 * TypeDeService type_service -- varchar(20) 
 * String ville_origine -- varchar(50)
 * String role -- varchar(20)
 * 
 */

public class JdbcAlibi {
	private static int num = 1;
//	private static final long serialVersionUID = 1L;
//	private static final String TMP_DIR_;
	public static int lastid() {
		return num++;
	}

	/*
	 * Affiche les champs "champs" pour la recherche dans "table" où s1='s2'. Si
	 * le tableau champs est vide, remplace par *. Ex : SELECT champs FROM table
	 * WHERE s1 = 's2'; SELECT * FROM groupe WHERE grp = 'admin';
	 */

	// Attention : on va peut-être devoir ajouter ORDER BY, etc. Mais peut-être,
	// hein ?
	public static Object[] recherche(ArrayList<String> champs,
			ArrayList<String> tables, ArrayList<Object> s1, ArrayList<Object> s2)
			throws SQLException, ClassNotFoundException, IOException {

		Properties props = new Properties();
		FileInputStream fis = new FileInputStream(
				"/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
		props.load(fis);

		String passwd = props.getProperty("passwd");
		String url = props.getProperty("url");
		String user = props.getProperty("user");

		// charger le driver
		Class.forName("org.postgresql.Driver");
		// se connecter à la base de données
		Connection conn = DriverManager.getConnection(url, user, passwd);
		// issue a query (create a statement and execute a query on it)
		Statement state = (Statement) conn.createStatement();

		String query = "SELECT ";
		int l = champs.size();
		if (l == 0) {
			query += "* ";
		} else {
			for (int i = 0; i < l - 1; i++) {
				query += champs.get(i) + ", ";
			}
			query += champs.get(l) + " ";
		}
		query += "FROM ";
		for (int j = 0; j < tables.size() - 1; j++) {
			query += tables.get(j) + ", ";
		}
		query += tables.get(tables.size() - 1) + " WHERE ";
		if (s1.size() != s2.size()) {
			System.err
					.println("Les deux listes de recherche doivent avoir la même longueur !");
			return null;
		} else {
			query += s1.get(0) + " = ";
			if (s2.get(0) instanceof Integer) {
				query += s2.get(0);
			} else {
				query += "'" + s2.get(0) + "'";
			}

			for (int i = 1; i < s1.size(); i++) {
				query += " AND " + s1.get(i) + " = ";
				if (s2.get(i) instanceof Integer) {
					query += s2.get(i);
				} else {
					query += "'" + s2.get(i) + "'";
				}

			}
			ResultSet result = state.executeQuery(query);
			Object[] r = transformeResult(result);
			result.close();
			state.close();
			return r;
		}
	}

	@SuppressWarnings("unchecked")
	private static Object[] transformeResult(ResultSet result)
			throws SQLException {
		ResultSetMetaData resultMeta = result.getMetaData();
		int nb_colonnes = resultMeta.getColumnCount();
		Object[] o = new Object[nb_colonnes];

		for (int i = 1; i <= nb_colonnes; i++) {
			o[i - 1] = new ArrayList<Object>();
			((ArrayList<Object>) o[i - 1]).add(resultMeta.getColumnName(i));
		}

		while (result.next()) {
			for (int i = 1; i <= nb_colonnes; i++) {
				((ArrayList<Object>) o[i - 1]).add(result.getObject(i));
			}
		}

		return o;
	}

	public static ResultSetMetaData rechercheMeta(ArrayList<String> champs,
			ArrayList<String> tables, ArrayList<Object> s1, ArrayList<Object> s2)
			throws SQLException, ClassNotFoundException, IOException {

		Properties props = new Properties();
		FileInputStream fis = new FileInputStream(
				"/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
		props.load(fis);

		String passwd = props.getProperty("passwd");
		String url = props.getProperty("url");
		String user = props.getProperty("user");

		// charger le driver
		Class.forName("org.postgresql.Driver");
		// se connecter à la base de données
		Connection conn = DriverManager.getConnection(url, user, passwd);
		// issue a query (create a statement and execute a query on it)
		Statement state = (Statement) conn.createStatement();

		String query = "SELECT ";
		int l = champs.size();
		if (l == 0) {

			
			query += "* ";
		} else {
			for (int i = 0; i < l - 1; i++) {
				query += champs.get(i) + ", ";
			}
			query += champs.get(l) + " ";
		}
		query += "FROM ";
		for (int j = 0; j < tables.size() - 1; j++) {
			query += tables.get(j) + ", ";
		}
		query += tables.get(tables.size() - 1) + " WHERE ";
		if (s1.size() != s2.size()) {
			System.err
					.println("Les deux listes de recherche doivent avoir la même longueur !");
			return null;
		} else {
			query += s1.get(0) + " = ";
			if (s2.get(0) instanceof Integer) {
				query += s2.get(0);
			} else {
				query += "'" + s2.get(0) + "'";
			}

			for (int i = 1; i < s1.size(); i++) {
				query += " AND " + s1.get(i) + " = ";
				if (s2.get(i) instanceof Integer) {
					query += s2.get(i);
				} else {
					query += "'" + s2.get(i) + "'";
				}

			}
			ResultSet result = state.executeQuery(query);
			result.close();
			ResultSetMetaData resultMeta = result.getMetaData();
			state.close();
			return resultMeta;
		}
	}

	/*
	 * Insère "s" dans la table alibi. int, String, Date, String, String, String
	 */
	public static void insere(Object[] s) throws SQLException,
			ClassNotFoundException, IOException {

		Properties props = new Properties();
		FileInputStream fis = new FileInputStream(
				"/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
		props.load(fis);

		String passwd = props.getProperty("passwd");
		String url = props.getProperty("url");
		String user = props.getProperty("user");

		// charger le driver
		Class.forName("org.postgresql.Driver");
		// se connecter à la base de données
		Connection conn = DriverManager.getConnection(url, user, passwd);
		// issue a query (create a statement and execute a query on it)
		Statement state = (Statement) conn.createStatement();

		// comparer la longueur de "s" avec le nombre de colonnes de la
		// table
		int l = s.length;
		if (l != 6) {
			System.err
					.println("Vous devez avoir 6 arguments de type int, String, Date, String, String, String.");
		} else if (!(s[0] instanceof Integer) || !(s[1] instanceof String)
				|| !(s[2] instanceof java.util.Date)
				|| !(s[3] instanceof String) || !(s[4] instanceof String)
				|| !(s[5] instanceof String)) {
			System.err.println("Vos arguments n'ont pas le bon type.");
		} else {
			String s2 = JdbcCommentaire.convertDate((java.util.Date) s[2]);

			// inserer
			String query = "INSERT INTO alibi VALUES (" + s[0] + ", '" + s[1]
					+ "', '" + s2 + "', '" + s[3] + "', '" + s[4] + "', '"
					+ s[5] + "')";
			state.executeUpdate(query);
			state.close();
		}
	}

	/**
	 * addAlibi ajoute un alibi dans la BD
	 * 
	 * @param id_annonce
	 * @param pseudo
	 *            du demandeur ou de celui propose.
	 * @param date_a
	 *            ou l'alibi doit se faire ou de la date de l'annonce.
	 * @param dispo_sem
	 *            disposition dans la semaine (semaine|weekend|toutletemps)
	 * @param dispo_jour
	 *            disposition dans la
	 *            journée(matin|midi|soir|après-midi|nimporte)
	 * @param type_service
	 *            (sms|mail|appel|carte_postale|rencontre)
	 * @param ville_origine
	 *            (ville dorigine d'une carten ou de la rencontre etc)
	 * @param role
	 *            (ami|conjoint...)
	 * @param details
	 * @param demande
	 *            (true ou false)
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws IOException
	 */
	public static void addAlibi(int id_annonce, String pseudo, String date_a,
			String dispo_sem, String dispo_jour, String type_service,
			String ville_origine, String role, String details, boolean demande)
			throws SQLException, ClassNotFoundException, IOException {

		Properties props = new Properties();
		FileInputStream fis = new FileInputStream(
				"/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
		props.load(fis);

		String passwd = props.getProperty("passwd");
		String url = props.getProperty("url");
		String user = props.getProperty("user");

		// charger le driver
		Class.forName("org.postgresql.Driver");
		// se connecter à la base de données
		Connection conn = DriverManager.getConnection(url, user, passwd);
		Statement stmt = conn.createStatement();

		stmt.executeUpdate("INSERT INTO alibi VALUES (" + id_annonce + ",'"
				+ pseudo + "','" + date_a + "','" + dispo_sem + "','"
				+ dispo_jour + "','" + type_service + "','" + ville_origine
				+ "','" + role + "','" + details + "','" + demande + "');");
		stmt.close();
	}

	/**
	 * efface une annonce de la base de donné.
	 * 
	 * @param num_annonce
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws IOException
	 */
	public static void deleteAnnonce(int num_annonce) throws SQLException,
			ClassNotFoundException, IOException {

		// supprimer dans la BD Alibi
		Properties props = new Properties();
		FileInputStream fis = new FileInputStream(
				"/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
		props.load(fis);

		String passwd = props.getProperty("passwd");
		String url = props.getProperty("url");
		String user = props.getProperty("user");

		// charger le driver
		Class.forName("org.postgresql.Driver");
		// se connecter à la base de données
		Connection conn = DriverManager.getConnection(url, user, passwd);
		Statement stmt = conn.createStatement();
		stmt.executeUpdate("DELETE FROM Alibi WHERE id_annonce = '"
				+ num_annonce + "'; ");
		stmt.close();

	}

	// public static void updateId(ArrayList<Object> champRecherche1,
	// ArrayList<Object> champRecherche2, int newValue)
	// throws SQLException, ClassNotFoundException, IOException {
	// ArrayList<String> champs = new ArrayList<String>();
	// ArrayList<String> tables = new ArrayList<String>();
	// tables.add("alibi");
	// ResultSet r = recherche(champs, tables, champRecherche1,
	// champRecherche2);
	// r.updateInt("id_annonce", newValue);
	// // result.updateXXX(nomColonne, value) value de typeXXX + updateRow()
	// r.updateRow();
	// }
	//
	// public static void updatePseudo(ArrayList<Object> champRecherche1,
	// ArrayList<Object> champRecherche2, String newValue)
	// throws SQLException, ClassNotFoundException, IOException {
	// ArrayList<String> champs = new ArrayList<String>();
	// ArrayList<String> tables = new ArrayList<String>();
	// tables.add("alibi");
	// ResultSet r = recherche(champs, tables, champRecherche1,
	// champRecherche2);
	// r.updateString("pseudo", newValue);
	// r.updateRow();
	// }
	//
	// public static void updateDate(ArrayList<Object> champRecherche1,
	// ArrayList<Object> champRecherche2, java.util.Date newValue)
	// throws SQLException, ClassNotFoundException, IOException {
	// ArrayList<String> champs = new ArrayList<String>();
	// ArrayList<String> tables = new ArrayList<String>();
	// tables.add("alibi");
	// ResultSet r = recherche(champs, tables, champRecherche1,
	// champRecherche2);
	// r.updateString("pseudo", JdbcCommentaire.convertDate(newValue));
	// r.updateRow();
	// }
	//
	// public static void updateService(ArrayList<Object> champRecherche1,
	// ArrayList<Object> champRecherche2, String newValue)
	// throws SQLException, ClassNotFoundException, IOException {
	// ArrayList<String> champs = new ArrayList<String>();
	// ArrayList<String> tables = new ArrayList<String>();
	// tables.add("alibi");
	// ResultSet r = recherche(champs, tables, champRecherche1,
	// champRecherche2);
	// r.updateString("type_service", newValue);
	// r.updateRow();
	// }
	//
	// public static void updateVille(ArrayList<Object> champRecherche1,
	// ArrayList<Object> champRecherche2, String newValue)
	// throws SQLException, ClassNotFoundException, IOException {
	// ArrayList<String> champs = new ArrayList<String>();
	// ArrayList<String> tables = new ArrayList<String>();
	// tables.add("alibi");
	// ResultSet r = recherche(champs, tables, champRecherche1,
	// champRecherche2);
	// r.updateString("ville_origine", newValue);
	// r.updateRow();
	// }
	//
	// public static void updateRole(ArrayList<Object> champRecherche1,
	// ArrayList<Object> champRecherche2, String newValue)
	// throws SQLException, ClassNotFoundException, IOException {
	// ArrayList<String> champs = new ArrayList<String>();
	// ArrayList<String> tables = new ArrayList<String>();
	// tables.add("alibi");
	// ResultSet r = recherche(champs, tables, champRecherche1,
	// champRecherche2);
	// r.updateString("role", newValue);
	// r.updateRow();
	// }

	/**
	 * chercheSms renvoi la liste des demande de sms
	 * 
	 * @return ArrayList<Alibi>
	 */

	/*
	 * public static ArrayList<Alibi> chercheSms() throws SQLException,
	 * ClassNotFoundException, IOException {
	 * 
	 * Properties props = new Properties(); FileInputStream fis = new
	 * FileInputStream(
	 * "/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
	 * props.load(fis);
	 * 
	 * String passwd = props.getProperty("passwd"); String url =
	 * props.getProperty("url"); String user = props.getProperty("user");
	 * 
	 * // charger le driver Class.forName("org.postgresql.Driver"); // se
	 * connecter à la base de données Connection conn =
	 * DriverManager.getConnection(url, user, passwd); Statement state =
	 * conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
	 * ResultSet.CONCUR_UPDATABLE); // Statement state2 = //
	 * conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, //
	 * ResultSet.CONCUR_UPDATABLE); // reccuperer la ligne a modifier String
	 * query = "SELECT * FROM alibi WHERE type_service = 'sms';"; ResultSet res
	 * = state.executeQuery(query); ArrayList<Alibi> annonces = new
	 * ArrayList<Alibi>(); // recupérer la liste des pseudos. while (res.next())
	 * { Alibi c = new Alibi();
	 * 
	 * annonces.add(c); } return annonces; }
	 */
	/**
	 * chercheMail renvoi la liste des demande de mail
	 * 
	 * @return ArrayList<Alibi>
	 */
	/*
	 * public static ArrayList<Alibi> chercheMail() throws SQLException,
	 * ClassNotFoundException, IOException {
	 * 
	 * Properties props = new Properties(); FileInputStream fis = new
	 * FileInputStream(
	 * "/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
	 * props.load(fis);
	 * 
	 * String passwd = props.getProperty("passwd"); String url =
	 * props.getProperty("url"); String user = props.getProperty("user");
	 * 
	 * // charger le driver Class.forName("org.postgresql.Driver"); // se
	 * connecter à la base de données Connection conn =
	 * DriverManager.getConnection(url, user, passwd); Statement state =
	 * conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
	 * ResultSet.CONCUR_UPDATABLE); // Statement state2 = //
	 * conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, //
	 * ResultSet.CONCUR_UPDATABLE); // reccuperer la ligne a modifier String
	 * query = "SELECT * FROM alibi WHERE type_service = 'email';"; ResultSet
	 * res = state.executeQuery(query); ArrayList<Alibi> annonces = new
	 * ArrayList<Alibi>(); // recupérer la liste des pseudos. while (res.next())
	 * { Alibi c = new Alibi(); annonces.add(c); } return annonces; }
	 */

	/**
	 * chercheLettre renvoi la liste des demande de lettre
	 * 
	 * @return ArrayList<Alibi>
	 */
	/*
	 * public static ArrayList<Alibi> chercheLettre() throws SQLException,
	 * ClassNotFoundException, IOException {
	 * 
	 * Properties props = new Properties(); FileInputStream fis = new
	 * FileInputStream(
	 * "/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
	 * props.load(fis);
	 * 
	 * String passwd = props.getProperty("passwd"); String url =
	 * props.getProperty("url"); String user = props.getProperty("user");
	 * 
	 * // charger le driver Class.forName("org.postgresql.Driver"); // se
	 * connecter à la base de données Connection conn =
	 * DriverManager.getConnection(url, user, passwd); Statement state =
	 * conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
	 * ResultSet.CONCUR_UPDATABLE); // Statement state2 = //
	 * conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, //
	 * ResultSet.CONCUR_UPDATABLE); // reccuperer la ligne a modifier String
	 * query = "SELECT * FROM alibi WHERE type_service = 'lettre';"; ResultSet
	 * res = state.executeQuery(query); ArrayList<Alibi> annonces = new
	 * ArrayList<Alibi>(); // recupérer la liste des pseudos. while (res.next())
	 * { Alibi c = new Alibi(); c.setPseudo(res.getString("pseudo"));
	 * c.setRole(""); c.setVille("ville");
	 * c.setDate(JdbcAlibi.toDate(res.getString("date_a"))); annonces.add(c); }
	 * 
	 * return annonces; }
	 * 
	 * public static String convertDate(java.util.Date d) { GregorianCalendar c
	 * = new GregorianCalendar(); c.setTime(d); int year = c.get(Calendar.YEAR);
	 * int month = c.get(Calendar.MONTH); int day =
	 * c.get(Calendar.DAY_OF_MONTH); int hour = c.get(Calendar.HOUR_OF_DAY); int
	 * minute = c.get(Calendar.MINUTE); int second = c.get(Calendar.SECOND);
	 * return (year + "-" + month + "-" + day + " " + hour + ":" + minute + ":"
	 * + second); }
	 */

	@SuppressWarnings("deprecation")
	public static Date toDate(String d) {
		Date res = new Date();
		res.setYear(Integer.parseInt("" + d.charAt(0) + d.charAt(1)
				+ d.charAt(2) + d.charAt(3)));
		res.setMonth(Integer.parseInt("" + d.charAt(5) + d.charAt(6)));

		return res;
	}

	/**
	 * chercheAlibiH renvoi la liste des utilisateur proposant un alibi humain
	 * selon le critères entré en paramètres:
	 * 
	 * @param genre
	 *            homme ou femme
	 * @param role
	 *            role joué
	 * @param dispoSem
	 *            disponibilité semaine, we ou tout le temps
	 * @param dispoJr
	 *            dispo dans la jrné matin, midi soir aprèm tout le temps
	 * @return ArrayList<Utilisateur>
	 */
	public static ArrayList<Utilisateur> chercheAlibiH(String genre,
			String role, String dispoSem, String dispoJr) throws SQLException,
			ClassNotFoundException, IOException {

		Properties props = new Properties();
		FileInputStream fis = new FileInputStream(
				"/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
		props.load(fis);

		String passwd = props.getProperty("passwd");
		String url = props.getProperty("url");
		String user = props.getProperty("user");

		// charger le driver
		Class.forName("org.postgresql.Driver");
		// se connecter à la base de données
		Connection conn = DriverManager.getConnection(url, user, passwd);
		Statement state = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
		// Statement state2 =
		// conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
		// ResultSet.CONCUR_UPDATABLE);
		// reccuperer la ligne a modifier
		String query = "SELECT pseudo FROM alibi WHERE type_service = 'rencontre' and role = '"
				+ role
				+ "' and dispo_sem = '"
				+ dispoSem
				+ "' and dispo_jour='"
				+ dispoJr + "' and demande='f'   ;";
		ResultSet res = state.executeQuery(query);
		ArrayList<String> alibih = new ArrayList<String>();
		// recupérer la liste des pseudos.
		while (res.next()) {
			alibih.add(res.getString("pseudo"));
		}
		state.close();
		res.close();
		ArrayList<Utilisateur> trouve = new ArrayList<Utilisateur>();
		int vx = 0;
		if (genre.equals("femme"))
			vx = 2;
		else if (genre.equals("homme"))
			vx = 1;
		int i = 0;
		Utilisateur cur;
		if (vx == 0) // sexe indifférent alors on ajoute tout le monde
			while (i < alibih.size()) {
				trouve.add(JdbcUtilisateur.findByPK(alibih.get(i)));
				i++;
			}
		else if (vx == 1)// que des hommes
			while (i < alibih.size()) {
				cur = JdbcUtilisateur.findByPK(alibih.get(i));
				if (cur.getSexe() == 1)
					trouve.add(cur);
				i++;
			}
		else
			// que des femmes
			while (i < alibih.size()) {
				cur = JdbcUtilisateur.findByPK(alibih.get(i));
				if (cur.getSexe() == 2)
					trouve.add(cur);
				i++;
			}

		return trouve;
	}

	/*
	 * public static ArrayList<Alibi> getAllAnnonces() throws SQLException,
	 * ClassNotFoundException, IOException { ArrayList<Alibi> trouve = new
	 * ArrayList<Alibi>(); Properties props = new Properties(); FileInputStream
	 * fis = new FileInputStream(
	 * "/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
	 * props.load(fis);
	 * 
	 * String passwd = props.getProperty("passwd"); String url =
	 * props.getProperty("url"); String user = props.getProperty("user");
	 * 
	 * // charger le driver Class.forName("org.postgresql.Driver"); // se
	 * connecter à la base de données Connection conn =
	 * DriverManager.getConnection(url, user, passwd); Statement state =
	 * conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
	 * ResultSet.CONCUR_UPDATABLE); // Statement state2 = //
	 * conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, //
	 * ResultSet.CONCUR_UPDATABLE); // reccuperer la ligne a modifier String
	 * query = "SELECT * FROM alibi;"; ResultSet res =
	 * state.executeQuery(query); while (res.next()) { Alibi an = new Alibi();
	 * an.setId_annonce(Integer.parseInt(res.getString("id_annonce")));
	 * an.setPseudo(res.getString("pseudo")); an.setRole(res.getString("role"));
	 * an.setDetails(res.getString("details"));
	 * an.setDemande(res.getBoolean("demande"));
	 * an.setVilleOrigine(res.getString("ville_origine")); an.setTyp } return
	 * trouve; }
	 * 
	 * 
	 * 
	 * date_a dispo_sem dispo_jour type_service ville_origine role details
	 * demande
	 */

}
